# -*- coding: utf-8 -*-
# @Time    : 2022/11/16 15:07
# @Author  : yangyuexiong
# @Email   : yang6333yyx@126.com
# @File    : main.py
# @Software: PyCharm

import json

from openpyxl import Workbook
from openpyxl.cell.read_only import ReadOnlyCell, EmptyCell

from commands.app_commands.Excel.excel_base.main import ExcelHandle, read_range_tuple


class ExcelReadHandle(ExcelHandle):
    """读取"""

    def __init__(self, excel_path=None, result_save_type=None, debug=None):
        super(ExcelReadHandle, self).__init__(excel_path=excel_path)

        self.debug = debug
        self.result_save_type = result_save_type  # 读取结果保存方式
        self.read_range_dict = {
            "cell": self.read_cell,
            "a_line": self.read_a_line,
            "one_column": self.read_one_column,
            "all": self.read_all,
            "custom_range": self.read_custom_range
        }

        self.none_dict = {
            "coordinate": "",
            "value": ""
        }

        self.expand = {
            "max_row": 0,
            "max_column": ""
        }

    def __slices(self, none_value_index, before_result_len):
        """
        判断有效数据末尾是否有空值并返回切片
        :param none_value_index: 空数据的索引list
        :param before_result_len: 处理之前结果集的长度
        :return:
        """

        slices = []
        sw = False
        if none_value_index:
            none_value_index.reverse()
            current_nvi = 0
            for index, nvi in enumerate(none_value_index):
                # print(nvi)
                if index == 0:  # 首次赋值
                    if nvi != before_result_len:
                        sw = True
                        break
                    current_nvi = nvi
                    slices.append(nvi)
                    continue
                if current_nvi and current_nvi - nvi == 1:
                    current_nvi = nvi
                    slices.append(nvi)
                else:
                    break
            return sw, slices
        else:
            return True, slices

    def set_expand(self, max_row=None, max_column=None):
        """设置扩展参数"""

        self.expand["max_row"] = max_row
        self.expand["max_column"] = max_column
        return self.expand

    def __cell_value(self, value):
        """1"""

        if not value:
            value = ""
        if not isinstance(value, (str, int, dict, list)):
            value = str(value)
        return value

    def check_cell(self, cell):
        """检验cell"""

        try:
            coordinate = cell.coordinate
            value = cell.value
            return True
        except BaseException as e:
            return False

    def untie_cell(self, cell):
        """解包cell"""

        coordinate = cell.coordinate
        value = cell.value
        value = self.__cell_value(value)
        result = {
            "coordinate": coordinate,
            "value": value,
        }
        return result, coordinate, value

    def read_cell(self, sheet, row_num, column_num, **kwargs):
        """
        读取单元格数据
        :param sheet: sheet对象
        :param row_num: 行号
        :param column_num: 列号
        :param kwargs:
        :return:
        """

        print('=== read_cell ===')
        c = sheet[f"{column_num}{row_num}"]
        if self.check_cell(cell=c):
            data, _, _ = self.untie_cell(cell=c)
            self.set_expand(max_row=row_num, max_column=column_num)
            return data
        else:
            return {"read_cell错误": c}

    def read_a_line(self, sheet, row_num, **kwargs):
        """
        读取一行数据
        :param sheet: sheet对象
        :param row_num: 行号
        :param kwargs:
        :return:
        """

        print('=== read_a_line ===')
        sheet_rows = list(sheet.rows)
        rn = int(row_num) - 1
        result = []
        none_value_index = []  # 空数据单元格的索引

        try:
            target_sheet_list = sheet_rows[rn]
        except:
            return result

        for index, cell in enumerate(target_sheet_list, 1):
            if self.check_cell(cell):
                data, coordinate, value = self.untie_cell(cell)
                if not value:
                    none_value_index.append(index)

                if self.debug:
                    print(index, data)

                result.append(data)

        sw, slices = self.__slices(none_value_index=none_value_index, before_result_len=len(result))

        if sw:  # 最后一个值之后没有空值
            pass
        else:  # 最后一个值之后面存在空值
            start_none_value_index = min(slices)
            end_none_value_index = max(slices)
            if self.debug:
                print(f"无效数据索引：{slices}")
                print(f"无效数据起点：{start_none_value_index}")
                print(f"无效数据结束：{end_none_value_index}")
                print(f"结果长度：{len(result)}")
            end_index = start_none_value_index - 1
            result = result[0:end_index]

        max_column = result[-1].get('coordinate')
        self.set_expand(max_column=max_column)
        return result

    def read_one_column(self, sheet, column_num, **kwargs):
        """
        读取一列数据
        :param sheet: sheet对象
        :param column_num: 列号
        :param kwargs:
        :return:
        """

        print('=== read_one_column ===')
        print(column_num)

        result = []
        result_len = 0
        none_value_index = []  # 空数据单元格的索引

        for cells in list(sheet.rows):
            for index, cell in enumerate(cells):

                if self.check_cell(cell):
                    data, coordinate, value = self.untie_cell(cell)
                    try:
                        """
                        column_num: A
                        A1 -> ["","1"] -> True
                        AA1 -> ["", "", "1"] -> False
                        """
                        q = coordinate.split(column_num)
                        if len(q) == 2 and not q[0] and int(q[-1]):
                            # print(coordinate, q)
                            cell_prefix = True
                            if not result_len:  # 设预期结果长度(未对结尾空数据处理前的长度)
                                result_len = len(cells)
                                # print(result_len)
                            if not value:
                                none_value_index.append(q)
                                # print(none_value_index)
                        else:
                            cell_prefix = False
                    except:
                        cell_prefix = False

                    if cell_prefix:

                        if self.debug:
                            print(data)

                        result.append(data)

        sw, slices = self.__slices(none_value_index=none_value_index, before_result_len=len(result))

        if sw:  # 最后一个值之后没有空值
            max_row = len(result) + 1
            self.set_expand(max_row=max_row)
        else:  # 最后一个值之后面存在空值
            start_none_value_index = min(slices)
            end_none_value_index = max(slices)
            if self.debug:
                print(f"无效数据索引：{slices}")
                print(f"无效数据起点：{start_none_value_index}")
                print(f"无效数据结束：{end_none_value_index}")
                print(f"结果长度：{len(result)}")
            result = result[0:start_none_value_index - 1]
            max_row = len(result) + 1
            self.set_expand(max_row=max_row)

        return result

    def read_all(self, sheet, **kwargs):
        """
        读取全表
        :param sheet: sheet对象
        :param kwargs:
        :return:
        """

        print('=== read_all ===')
        result = []
        max_row = 0
        column_dict = {}
        for row in sheet.iter_rows():
            for index, cell in enumerate(row, 1):
                if self.check_cell(cell):
                    data, coordinate, value = self.untie_cell(cell)

                    if coordinate and value:
                        column_dict[index] = coordinate

                    if self.debug:
                        print(data)

                    result.append(data)

                else:
                    result.append(self.none_dict)
            max_row += 1

        # print(json.dumps(column_dict, ensure_ascii=False))
        max_column = column_dict.get(list(column_dict.keys())[-1])
        self.set_expand(max_row=max_row, max_column=max_column)
        return result

    def read_custom_range(self, sheet, **kwargs):
        """
        读取自定义区间
        :param sheet: sheet对象
        :param kwargs: 起始行列，末尾行列
            {
                "start_row_num":"1",
                "start_column_num":"A",
                "end_row_num":"3",
                "end_column_num":"C",
            }
        """

        print('=== read_custom_range ===')
        print(kwargs)
        is_sort = kwargs.get('is_sort')

        result = []
        try:
            if is_sort:
                start_column = kwargs.get('start_column')
                end_column = kwargs.get('end_column')
                obj_list = sheet[start_column:end_column]
            else:
                start_row_num = int(kwargs.get('start_row_num'))
                start_column_num = kwargs.get('start_column_num')
                end_row_num = int(kwargs.get('end_row_num'))
                end_column_num = kwargs.get('end_column_num')
                obj_list = sheet[f'{start_column_num}{start_row_num}':f'{end_column_num}{end_row_num}']
                self.set_expand(end_row_num, end_column_num)

        except BaseException as e:
            msg = f'读取失败：自定义区间错误，请检查'
            print(msg)
            self.throw['status'] = False
            self.throw['message'] = msg
            return False

        for tup_index, tup in enumerate(obj_list):
            if is_sort and tup_index == 0:
                continue
            for cell in tup:
                if self.check_cell(cell):
                    data, _, _ = self.untie_cell(cell)
                    result.append(data)
                else:
                    result.append(self.none_dict)

        return result

    def excel_read(self, sheet_name=None, read_range=None, **kwargs):
        """
        读取
        :param sheet_name: sheet名称
        :param read_range: 范围类型
        :param kwargs: 对应范围类型的扩展参数
        cell:
            行号，列号
            {
                "row_num":"1",
                "column_num":"A",
            }
        a_line:
            行号
            {
                "row_num":"1"
            }
        one_column:
            列号
            {
                "column_num":"A",
            }
        custom_range:
            起始行列，末尾行列
            {
                "start_row_num":"1",
                "start_column_num":"A",
                "end_row_num":"3",
                "end_column_num":"C",
            }
        all:
            全表
            {}
        :return:
        """

        try:
            if isinstance(self.wb, Workbook):

                print(f"读取:{self.excel_path.split('/')[-1]}")

                if read_range not in read_range_tuple:
                    msg = f'读取范围错误:{read_range}'
                    print(msg)
                    self.throw['status'] = False
                    self.throw['message'] = msg
                    return self.throw

                if not self.check_sheet_name(sheet_name):
                    return self.throw

                sheet = self.get_sheet(sheet_name)
                kwargs['sheet'] = sheet
                read_func = self.read_range_dict.get(read_range)
                result = read_func(**kwargs)
                if self.debug:
                    print('=== to json ===')
                    print(json.dumps(result, ensure_ascii=False))
                self.throw['message'] = '操作成功'
                self.throw['data'] = result
                self.throw['expand'] = self.expand
                return self.throw

        except BaseException as e:
            print(e)
            self.return_error_file()
            return self.throw
